package com.ruibang.glass.quality.service.impl;

import com.alibaba.excel.util.StringUtils;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.ruibang.glass.common.domain.file.FileBusiness;
import com.ruibang.glass.quality.domain.req.HorizontalTrayReq;
import com.ruibang.glass.quality.entity.HorizontalTray;
import com.ruibang.glass.quality.feign.FileApi;
import com.ruibang.glass.quality.mapper.HorizontalTrayMapper;
import com.ruibang.glass.quality.service.HorizontalTrayService;
import com.ruibang.glass.quality.util.PoiUtils;
import com.teaming.cloud.framework2.common.model.ResultBody;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

/**
 * <p>
 * 来料-水平托盘 服务实现类
 * </p>
 *
 * @author songJinKang
 * @since 2024-05-21
 */
@Slf4j
@Service
public class HorizontalTrayServiceImpl extends ServiceImpl<HorizontalTrayMapper, HorizontalTray> implements HorizontalTrayService {

    @Autowired
    private FileApi fileApi;

    @Override
    public IPage<HorizontalTray> pageQuery(HorizontalTrayReq horizontalTrayReq) {
        return null;
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveInfo(HorizontalTray horizontalTray) {
        //保存
        this.save(horizontalTray);
        //保存文件
        if (CollectionUtils.isNotEmpty(horizontalTray.getFileIds())) {
            FileBusiness fileBusiness = new FileBusiness();
            fileBusiness.setFileIds(horizontalTray.getFileIds());
            fileBusiness.setBusinessId(horizontalTray.getId());
            fileApi.updateBusinessById(fileBusiness);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void edit(HorizontalTray horizontalTray) {
        //修改
        this.updateById(horizontalTray);
        //修改文件
        if (CollectionUtils.isNotEmpty(horizontalTray.getFileIds())) {
            FileBusiness fileBusiness = new FileBusiness();
            fileBusiness.setFileIds(horizontalTray.getFileIds());
            fileBusiness.setBusinessId(horizontalTray.getId());
            fileApi.updateBusinessById(fileBusiness);
        }
    }

    @Override
    public HorizontalTray importExcel(MultipartFile file) {
        if (file != null) {
            //上传文件
            ResultBody<String> resultBody = fileApi.uploadFile(file);
            HorizontalTray horizontalTray = new HorizontalTray();
            if (resultBody != null && StringUtils.isNotBlank(resultBody.getData())) {
                horizontalTray.setUpLoadFileId(resultBody.getData());
            }


            // 创建一个 XSSFWorkbook 对象
            Workbook workbook = null;
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
            } catch (Exception e) {
                //如果不是excel直接上传文件返回
                return horizontalTray;
            }


            // 获取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);


            //型号
            String type = PoiUtils.getValue(sheet, 1, 3);
            horizontalTray.setType(type);

            //来料日期
            String incomingDate = PoiUtils.getValue(sheet, 1, 7);
            horizontalTray.setIncomingDate(incomingDate);

            //托盘号
            String palletNumber = PoiUtils.getValue(sheet, 2, 3);
            horizontalTray.setPalletNumber(palletNumber);

            //检查日期
            String checkDate = PoiUtils.getValue(sheet, 2, 7);
            horizontalTray.setCheckDate(checkDate);

            //供应商名称
            String supplierName = PoiUtils.getValue(sheet, 3, 3);
            horizontalTray.setSupplierName(supplierName);

            //检查人名称
            String checkUserName = PoiUtils.getValue(sheet, 3, 7);
            horizontalTray.setCheckUserName(checkUserName);

            //判定
            String vote = PoiUtils.getValue(sheet, 2, 8);
            horizontalTray.setVote(vote);

            //规格1
            String spec1 = PoiUtils.getValue(sheet, 5, 3);
            String data1 = PoiUtils.getValue(sheet, 5, 6);
            String vote1 = PoiUtils.getValue(sheet, 5, 8);
            String mark1 = PoiUtils.getValue(sheet, 5, 9);
            horizontalTray.setF1Spec(spec1);
            horizontalTray.setF1Data(data1);
            horizontalTray.setF1Vote(vote1);
            horizontalTray.setF1Remark(mark1);


            //规格2
            String spec2 = PoiUtils.getValue(sheet, 6, 3);
            String data2 = PoiUtils.getValue(sheet, 6, 6);
            String vote2 = PoiUtils.getValue(sheet, 6, 8);
            String mark2 = PoiUtils.getValue(sheet, 6, 9);
            horizontalTray.setF2Spec(spec1);
            horizontalTray.setF2Data(data1);
            horizontalTray.setF2Vote(vote1);
            horizontalTray.setF2Remark(mark1);


            //规格3
            String spec3 = PoiUtils.getValue(sheet, 7, 3);
            String data3 = PoiUtils.getValue(sheet, 7, 6);
            String vote3 = PoiUtils.getValue(sheet, 7, 8);
            String mark3 = PoiUtils.getValue(sheet, 7, 9);
            horizontalTray.setF3Spec(spec3);
            horizontalTray.setF3Data(data3);
            horizontalTray.setF3Vote(vote3);
            horizontalTray.setF3Remark(mark3);


            //规格4
            String spec4 = PoiUtils.getValue(sheet, 8, 3);
            String data4 = PoiUtils.getValue(sheet, 8, 6);
            String vote4 = PoiUtils.getValue(sheet, 8, 8);
            String mark4 = PoiUtils.getValue(sheet, 8, 9);
            horizontalTray.setF4Spec(spec4);
            horizontalTray.setF4Data(data4);
            horizontalTray.setF4Vote(vote4);
            horizontalTray.setF4Remark(mark4);


            //规格5
            String spec5 = PoiUtils.getValue(sheet, 9, 3);
            String data5 = PoiUtils.getValue(sheet, 9, 6);
            String vote5 = PoiUtils.getValue(sheet, 9, 8);
            String mark5 = PoiUtils.getValue(sheet, 9, 9);
            horizontalTray.setF5Spec(spec5);
            horizontalTray.setF5Data(data5);
            horizontalTray.setF5Vote(vote5);
            horizontalTray.setF5Remark(mark5);


            //规格6
            String spec6 = PoiUtils.getValue(sheet, 10, 3);
            String data6 = PoiUtils.getValue(sheet, 10, 6);
            String vote6 = PoiUtils.getValue(sheet, 10, 8);
            String mark6 = PoiUtils.getValue(sheet, 10, 9);
            horizontalTray.setF6Spec(spec6);
            horizontalTray.setF6Data(data6);
            horizontalTray.setF6Vote(vote6);
            horizontalTray.setF6Remark(mark6);


            //规格7
            String spec7 = PoiUtils.getValue(sheet, 11, 3);
            String data7 = PoiUtils.getValue(sheet, 11, 6);
            String vote7 = PoiUtils.getValue(sheet, 11, 8);
            String mark7 = PoiUtils.getValue(sheet, 11, 9);
            horizontalTray.setF7Spec(spec7);
            horizontalTray.setF7Data(data7);
            horizontalTray.setF7Vote(vote7);
            horizontalTray.setF7Remark(mark7);


            //规格8
            String spec8 = PoiUtils.getValue(sheet, 12, 3);
            String data8 = PoiUtils.getValue(sheet, 12, 6);
            String vote8 = PoiUtils.getValue(sheet, 12, 8);
            String mark8 = PoiUtils.getValue(sheet, 12, 9);
            horizontalTray.setF8Spec(spec8);
            horizontalTray.setF8Data(data8);
            horizontalTray.setF8Vote(vote8);
            horizontalTray.setF8Remark(mark8);


            //规格9
            String spec9 = PoiUtils.getValue(sheet, 13, 3);
            String data9 = PoiUtils.getValue(sheet, 13, 6);
            String vote9 = PoiUtils.getValue(sheet, 13, 8);
            String mark9 = PoiUtils.getValue(sheet, 13, 9);
            horizontalTray.setF9Spec(spec9);
            horizontalTray.setF9Data(data9);
            horizontalTray.setF9Vote(vote9);
            horizontalTray.setF9Remark(mark9);


            //规格10
            String spec10 = PoiUtils.getValue(sheet, 14, 3);
            String data10 = PoiUtils.getValue(sheet, 14, 6);
            String vote10 = PoiUtils.getValue(sheet, 14, 8);
            String mark10 = PoiUtils.getValue(sheet, 14, 9);
            horizontalTray.setF10Spec(spec10);
            horizontalTray.setF10Data(data10);
            horizontalTray.setF10Vote(vote10);
            horizontalTray.setF10Remark(mark10);


            //规格11
            String spec11 = PoiUtils.getValue(sheet, 15, 3);
            String data11 = PoiUtils.getValue(sheet, 15, 6);
            String vote11 = PoiUtils.getValue(sheet, 15, 8);
            String mark11 = PoiUtils.getValue(sheet, 15, 9);
            horizontalTray.setF11Spec(spec11);
            horizontalTray.setF11Data(data11);
            horizontalTray.setF11Vote(vote11);
            horizontalTray.setF11Remark(mark11);


            //规格12
            String spec12 = PoiUtils.getValue(sheet, 16, 3);
            String data12 = PoiUtils.getValue(sheet, 16, 6);
            String vote12 = PoiUtils.getValue(sheet, 16, 8);
            String mark12 = PoiUtils.getValue(sheet, 16, 9);
            horizontalTray.setF12Spec(spec12);
            horizontalTray.setF12Data(data12);
            horizontalTray.setF12Vote(vote12);
            horizontalTray.setF12Remark(mark12);


            //规格13
            String spec13 = PoiUtils.getValue(sheet, 17, 3);
            String data13 = PoiUtils.getValue(sheet, 17, 6);
            String vote13 = PoiUtils.getValue(sheet, 17, 8);
            String mark13 = PoiUtils.getValue(sheet, 17, 9);
            horizontalTray.setF13Spec(spec13);
            horizontalTray.setF13Data(data13);
            horizontalTray.setF13Vote(vote13);
            horizontalTray.setF13Remark(mark13);


            //规格14
            String spec14 = PoiUtils.getValue(sheet, 18, 3);
            String data14 = PoiUtils.getValue(sheet, 18, 6);
            String vote14 = PoiUtils.getValue(sheet, 18, 8);
            String mark14 = PoiUtils.getValue(sheet, 18, 9);
            horizontalTray.setF14Spec(spec14);
            horizontalTray.setF14Data(data14);
            horizontalTray.setF14Vote(vote14);
            horizontalTray.setF14Remark(mark14);

            //部件1
            String spec15 = PoiUtils.getValue(sheet, 19, 3);
            String data15 = PoiUtils.getValue(sheet, 19, 6);
            String vote15 = PoiUtils.getValue(sheet, 19, 8);
            String mark15 = PoiUtils.getValue(sheet, 19, 9);
            horizontalTray.setF15Spec(spec15);
            horizontalTray.setF15Data(data15);
            horizontalTray.setF15Vote(vote15);
            horizontalTray.setF15Remark(mark15);


            //部件2
            String spec16 = PoiUtils.getValue(sheet, 20, 3);
            String data16 = PoiUtils.getValue(sheet, 20, 6);
            String vote16 = PoiUtils.getValue(sheet, 20, 8);
            String mark16 = PoiUtils.getValue(sheet, 20, 9);
            horizontalTray.setF16Spec(spec16);
            horizontalTray.setF16Data(data16);
            horizontalTray.setF16Vote(vote16);
            horizontalTray.setF16Remark(mark16);


            //部件3
            String spec17 = PoiUtils.getValue(sheet, 21, 3);
            String data17 = PoiUtils.getValue(sheet, 21, 6);
            String vote17 = PoiUtils.getValue(sheet, 21, 8);
            String mark17 = PoiUtils.getValue(sheet, 21, 9);
            horizontalTray.setF17Spec(spec17);
            horizontalTray.setF17Data(data17);
            horizontalTray.setF17Vote(vote17);
            horizontalTray.setF17Remark(mark17);


            //部件4
            String spec18 = PoiUtils.getValue(sheet, 22, 3);
            String data18 = PoiUtils.getValue(sheet, 22, 6);
            String vote18 = PoiUtils.getValue(sheet, 22, 8);
            String mark18 = PoiUtils.getValue(sheet, 22, 9);
            horizontalTray.setF18Spec(spec18);
            horizontalTray.setF18Data(data18);
            horizontalTray.setF18Vote(vote18);
            horizontalTray.setF18Remark(mark18);


            //部件5
            String spec19 = PoiUtils.getValue(sheet, 23, 3);
            String data19 = PoiUtils.getValue(sheet, 23, 6);
            String vote19 = PoiUtils.getValue(sheet, 23, 8);
            String mark19 = PoiUtils.getValue(sheet, 23, 9);
            horizontalTray.setF19Spec(spec19);
            horizontalTray.setF19Data(data19);
            horizontalTray.setF19Vote(vote19);
            horizontalTray.setF19Remark(mark19);


            //托盘1
            String spec20 = getSizeCheckSPceName(sheet, 26);
            String data20L = PoiUtils.getValue(sheet, 26, 6);
            String data20R = PoiUtils.getValue(sheet, 26, 7);
            String vote20 = PoiUtils.getValue(sheet, 26, 8);
            String mark20 = PoiUtils.getValue(sheet, 26, 9);
            horizontalTray.setF20DataL(data20L);
            horizontalTray.setF20DataR(data20R);
            horizontalTray.setF20SpecL(spec20.concat("前/左"));
            horizontalTray.setF20SpecR(spec20.concat("后/右"));
            horizontalTray.setF20RemarkL(mark20);
            horizontalTray.setF20RemarkR(mark20);
            horizontalTray.setF20VoteR(vote20);
            horizontalTray.setF20VoteL(vote20);


            //托盘2
            String spec21 = getSizeCheckSPceName(sheet, 27);
            String data21L = PoiUtils.getValue(sheet, 27, 6);
            String data21R = PoiUtils.getValue(sheet, 27, 7);
            String vote21 = PoiUtils.getValue(sheet, 27, 8);
            String mark21 = PoiUtils.getValue(sheet, 27, 9);
            horizontalTray.setF21DataL(data21L);
            horizontalTray.setF21DataR(data21R);
            horizontalTray.setF21SpecL(spec21.concat("前/左"));
            horizontalTray.setF21SpecR(spec21.concat("后/右"));
            horizontalTray.setF21RemarkL(mark21);
            horizontalTray.setF21RemarkR(mark21);
            horizontalTray.setF21VoteR(vote21);
            horizontalTray.setF21VoteL(vote21);
            //托盘3
            String spec22 = getSizeCheckSPceName(sheet, 28);
            String data22L = PoiUtils.getValue(sheet, 28, 6);
            String data22R = PoiUtils.getValue(sheet, 28, 7);
            String vote22 = PoiUtils.getValue(sheet, 28, 8);
            String mark22 = PoiUtils.getValue(sheet, 28, 9);
            horizontalTray.setF22DataL(data22L);
            horizontalTray.setF22DataR(data22R);
            horizontalTray.setF22SpecL(spec22.concat("前/左"));
            horizontalTray.setF22SpecR(spec22.concat("后/右"));
            horizontalTray.setF22RemarkL(mark22);
            horizontalTray.setF22RemarkR(mark22);
            horizontalTray.setF22VoteR(vote22);
            horizontalTray.setF22VoteL(vote22);
            //托盘4
            String spec23 = getSizeCheckSPceName(sheet, 29);
            String data23L = PoiUtils.getValue(sheet, 29, 6);
            String data23R = PoiUtils.getValue(sheet, 29, 7);
            String vote23 = PoiUtils.getValue(sheet, 29, 8);
            String mark23 = PoiUtils.getValue(sheet, 29, 9);
            horizontalTray.setF23DataL(data23L);
            horizontalTray.setF23DataR(data23R);
            horizontalTray.setF23SpecL(spec23.concat("前/左"));
            horizontalTray.setF23SpecR(spec23.concat("后/右"));
            horizontalTray.setF23RemarkL(mark23);
            horizontalTray.setF23RemarkR(mark23);
            horizontalTray.setF23VoteR(vote23);
            horizontalTray.setF23VoteL(vote23);
            //托盘5
            String spec24 = getSizeCheckSPceName(sheet, 30);
            String data24L = PoiUtils.getValue(sheet, 30, 6);
            String data24R = PoiUtils.getValue(sheet, 30, 7);
            String vote24 = PoiUtils.getValue(sheet, 30, 8);
            String mark24 = PoiUtils.getValue(sheet, 30, 9);
            horizontalTray.setF24DataL(data24L);
            horizontalTray.setF24DataR(data24R);
            horizontalTray.setF24SpecL(spec24.concat("前/左"));
            horizontalTray.setF24SpecR(spec24.concat("后/右"));
            horizontalTray.setF24RemarkL(mark24);
            horizontalTray.setF24RemarkR(mark24);
            horizontalTray.setF24VoteR(vote24);
            horizontalTray.setF24VoteL(vote24);
            //托盘6
            String spec25 = getSizeCheckSPceName(sheet, 31);
            String data25L = PoiUtils.getValue(sheet, 31, 6);
            String data25R = PoiUtils.getValue(sheet, 31, 7);
            String vote25 = PoiUtils.getValue(sheet, 31, 8);
            String mark25 = PoiUtils.getValue(sheet, 31, 9);
            horizontalTray.setF25DataL(data25L);
            horizontalTray.setF25DataR(data25R);
            horizontalTray.setF25SpecL(spec25.concat("前/左"));
            horizontalTray.setF25SpecR(spec25.concat("后/右"));
            horizontalTray.setF25RemarkL(mark25);
            horizontalTray.setF25RemarkR(mark25);
            horizontalTray.setF25VoteR(vote25);
            horizontalTray.setF25VoteL(vote25);


            //EVA底板1
            String spec26 = getSizeCheckSPceName(sheet, 32);
            String data26L = PoiUtils.getValue(sheet, 32, 6);
            String data26R = PoiUtils.getValue(sheet, 32, 7);
            String vote26 = PoiUtils.getValue(sheet, 32, 8);
            String mark26 = PoiUtils.getValue(sheet, 32, 9);
            horizontalTray.setF26DataL(data26L);
            horizontalTray.setF26DataR(data26R);
            horizontalTray.setF26SpecL(spec26.concat("前/左"));
            horizontalTray.setF26SpecR(spec26.concat("后/右"));
            horizontalTray.setF26RemarkL(mark26);
            horizontalTray.setF26RemarkR(mark26);
            horizontalTray.setF26VoteR(vote26);
            horizontalTray.setF26VoteL(vote26);
            //EVA底板2
            String spec27 = getSizeCheckSPceName(sheet, 33);
            String data27L = PoiUtils.getValue(sheet, 33, 6);
            String data27R = PoiUtils.getValue(sheet, 33, 7);
            String vote27 = PoiUtils.getValue(sheet, 33, 8);
            String mark27 = PoiUtils.getValue(sheet, 33, 9);
            horizontalTray.setF27DataL(data27L);
            horizontalTray.setF27DataR(data27R);
            horizontalTray.setF27SpecL(spec27.concat("前/左"));
            horizontalTray.setF27SpecR(spec27.concat("后/右"));
            horizontalTray.setF27RemarkL(mark27);
            horizontalTray.setF27RemarkR(mark27);
            horizontalTray.setF27VoteR(vote27);
            horizontalTray.setF27VoteL(vote27);
            //EVA底板3
            String spec28 = getSizeCheckSPceName(sheet, 34);
            String data28L = PoiUtils.getValue(sheet, 34, 6);
            String data28R = PoiUtils.getValue(sheet, 34, 7);
            String vote28 = PoiUtils.getValue(sheet, 34, 8);
            String mark28 = PoiUtils.getValue(sheet, 34, 9);
            horizontalTray.setF28DataL(data28L);
            horizontalTray.setF28DataR(data28R);
            horizontalTray.setF28SpecL(spec28.concat("前/左"));
            horizontalTray.setF28SpecR(spec28.concat("后/右"));
            horizontalTray.setF28RemarkL(mark28);
            horizontalTray.setF28RemarkR(mark28);
            horizontalTray.setF28VoteR(vote28);
            horizontalTray.setF28VoteL(vote28);
            //EVA底板4
            String spec29 = getSizeCheckSPceName(sheet, 35);
            String data29L = PoiUtils.getValue(sheet, 35, 6);
            String data29R = PoiUtils.getValue(sheet, 35, 7);
            String vote29 = PoiUtils.getValue(sheet, 35, 8);
            String mark29 = PoiUtils.getValue(sheet, 35, 9);
            horizontalTray.setF29DataL(data29L);
            horizontalTray.setF29DataR(data29R);
            horizontalTray.setF29SpecL(spec29.concat("前/左"));
            horizontalTray.setF29SpecR(spec29.concat("后/右"));
            horizontalTray.setF29RemarkL(mark29);
            horizontalTray.setF29RemarkR(mark29);
            horizontalTray.setF29VoteR(vote29);
            horizontalTray.setF29VoteL(vote29);
            //EVA底板5
            String spec30 = getSizeCheckSPceName(sheet, 36);
            String data30L = PoiUtils.getValue(sheet, 36, 6);
            String data30R = PoiUtils.getValue(sheet, 36, 7);
            String vote30 = PoiUtils.getValue(sheet, 36, 8);
            String mark30 = PoiUtils.getValue(sheet, 36, 9);
            horizontalTray.setF30DataL(data30L);
            horizontalTray.setF30DataR(data30R);
            horizontalTray.setF30SpecL(spec30.concat("前/左"));
            horizontalTray.setF30SpecR(spec30.concat("后/右"));
            horizontalTray.setF30RemarkL(mark30);
            horizontalTray.setF30RemarkR(mark30);
            horizontalTray.setF30VoteR(vote30);
            horizontalTray.setF30VoteL(vote30);
            //EVA底板6
            String spec31 = getSizeCheckSPceName(sheet, 37);
            String data31L = PoiUtils.getValue(sheet, 37, 6);
            String data31R = PoiUtils.getValue(sheet, 37, 7);
            String vote31 = PoiUtils.getValue(sheet, 37, 8);
            String mark31 = PoiUtils.getValue(sheet, 37, 9);
            horizontalTray.setF31DataL(data31L);
            horizontalTray.setF31DataR(data31R);
            horizontalTray.setF31SpecL(spec31.concat("前/左"));
            horizontalTray.setF31SpecR(spec31.concat("后/右"));
            horizontalTray.setF31RemarkL(mark31);
            horizontalTray.setF31RemarkR(mark31);
            horizontalTray.setF31VoteR(vote31);
            horizontalTray.setF31VoteL(vote31);


            //盖板1
            String spec32 = getSizeCheckSPceName(sheet, 38);
            String data32L = PoiUtils.getValue(sheet, 38, 6);
            String data32R = PoiUtils.getValue(sheet, 38, 7);
            String vote32 = PoiUtils.getValue(sheet, 38, 8);
            String mark32 = PoiUtils.getValue(sheet, 38, 9);
            horizontalTray.setF32DataL(data32L);
            horizontalTray.setF32DataR(data32R);
            horizontalTray.setF32SpecL(spec32.concat("前/左"));
            horizontalTray.setF32SpecR(spec32.concat("后/右"));
            horizontalTray.setF32RemarkL(mark32);
            horizontalTray.setF32RemarkR(mark32);
            horizontalTray.setF32VoteR(vote32);
            horizontalTray.setF32VoteL(vote32);
            //盖板2
            String spec33 = getSizeCheckSPceName(sheet, 39);
            String data33L = PoiUtils.getValue(sheet, 39, 6);
            String data33R = PoiUtils.getValue(sheet, 39, 7);
            String vote33 = PoiUtils.getValue(sheet, 39, 8);
            String mark33 = PoiUtils.getValue(sheet, 39, 9);
            horizontalTray.setF33DataL(data33L);
            horizontalTray.setF33DataR(data33R);
            horizontalTray.setF33SpecL(spec33.concat("前/左"));
            horizontalTray.setF33SpecR(spec33.concat("后/右"));
            horizontalTray.setF33RemarkL(mark33);
            horizontalTray.setF33RemarkR(mark33);
            horizontalTray.setF33VoteR(vote33);
            horizontalTray.setF33VoteL(vote33);

            //压杆1
            String spec34 = getSizeCheckSPceName(sheet, 40);
            String data34L = PoiUtils.getValue(sheet, 40, 6);
            String data34R = PoiUtils.getValue(sheet, 40, 7);
            String vote34 = PoiUtils.getValue(sheet, 40, 8);
            String mark34 = PoiUtils.getValue(sheet, 40, 9);
            horizontalTray.setF34DataL(data34L);
            horizontalTray.setF34DataR(data34R);
            horizontalTray.setF34SpecL(spec34.concat("前/左"));
            horizontalTray.setF34SpecR(spec34.concat("后/右"));
            horizontalTray.setF34RemarkL(mark34);
            horizontalTray.setF34RemarkR(mark34);
            horizontalTray.setF34VoteR(vote34);
            horizontalTray.setF34VoteL(vote34);

            //EVA硬度1
            String spec35 = getSizeCheckSPceName(sheet, 41);
            String data35L = PoiUtils.getValue(sheet, 41, 6);
            String data35R = PoiUtils.getValue(sheet, 41, 7);
            String vote35 = PoiUtils.getValue(sheet, 41, 8);
            String mark35 = PoiUtils.getValue(sheet, 41, 9);
            horizontalTray.setF35DataL(data35L);
            horizontalTray.setF35DataR(data35R);
            horizontalTray.setF35SpecL(spec35.concat("前/左"));
            horizontalTray.setF35SpecR(spec35.concat("后/右"));
            horizontalTray.setF35RemarkL(mark35);
            horizontalTray.setF35RemarkR(mark35);
            horizontalTray.setF35VoteR(vote35);
            horizontalTray.setF35VoteL(vote35);
            //EVA硬度2
            String spec36 = getSizeCheckSPceName(sheet, 42);
            String data36L = PoiUtils.getValue(sheet, 42, 6);
            String data36R = PoiUtils.getValue(sheet, 42, 7);
            String vote36 = PoiUtils.getValue(sheet, 42, 8);
            String mark36 = PoiUtils.getValue(sheet, 42, 9);
            horizontalTray.setF36DataL(data36L);
            horizontalTray.setF36DataR(data36R);
            horizontalTray.setF36SpecL(spec36.concat("前/左"));
            horizontalTray.setF36SpecR(spec36.concat("后/右"));
            horizontalTray.setF36RemarkL(mark36);
            horizontalTray.setF36RemarkR(mark36);
            horizontalTray.setF36VoteR(vote36);
            horizontalTray.setF36VoteL(vote36);

            return horizontalTray;
        }

        return null;
    }


    private String getSizeCheckSPceName(Sheet sheet, int row) {
        String mc = PoiUtils.getValue(sheet, row, 3);
        String bz = PoiUtils.getValue(sheet, row, 4);
        String gc = PoiUtils.getValue(sheet, row, 5);
        StringBuilder stringBuilder = new StringBuilder();
        if (StringUtils.isNotBlank(mc)) {
            stringBuilder.append(mc).append("-");
        }
        if (StringUtils.isNotBlank(bz)) {
            stringBuilder.append(bz);
        }
        if (StringUtils.isNotBlank(gc)) {
            stringBuilder.append("(").append(gc).append(")");
        }
        return stringBuilder.toString();
    }
}
